<?php
/** 
 * Intervention_model
 * This class handles model work necessary for Intervention Crud and List operations 
 * 
 * @author Ray Nowell
 *	
 */ 
class Intervention_model extends MY_Model {

	function Intervention_model()
	{
		parent::MY_Model();
	}
	
	function Add_intervention($data)
	{
		return $this->db->insert('clinical_intervention', $data);
	}
	
	function Update_intervention($data)
	{
		$this->db->where('intervention_id',element('intervention_id', $data));
		$this->db->where('facility_id',$this->facility_id);
		return $this->db->update('clinical_intervention',$data);
	}
	
	function Delete_intervention()
	{
		$ret_val = $this->delete_record('clinical_intervention','intervention_id');
		return $ret_val;
	}
	
	function Copy_intervention($copy_to_next_month,$copy_date,$intervention_id_list)
	{
		if ($copy_to_next_month)
			$intervention_date_field	=	'DATE_ADD(intervention_date, INTERVAL 1 MONTH)';
		else
			$intervention_date_field	=	'\''.$copy_date.'\'';	
		
		$user = $this->ion_auth->get_user();
		
		//Use this query to insert the intervention		
		$sql	=	'INSERT INTO clinical_intervention 
					(
					 `patient_id`,`patient_id_number`,`intervention_date`,`intervention_code_id`,
					 `comment`,`status`,`physician_id`,`unit_patient_location_id`,`pharmacist_id`,`ordered_drug_id`,
					 `ordered_drug_text`,`ordered_unit_cost`,`ordered_drug_frequency_id`,`ordered_doses_per_day`,
					 `recommended_drug_id`,`recommended_drug_text`,`recommended_unit_cost`,
					 `recommended_drug_frequency_id`,`recommended_doses_per_day`,`days_of_tx`,`current_tx_day`,
					 `facility_id`,`created_date`,`created_by`,`modified_date`,`modified_by`
					 )
					 SELECT 
					 `patient_id`,`patient_id_number`,
					 '.$intervention_date_field.' AS intervention_date,
					 `intervention_code_id`,`comment`,`status`,`physician_id`,`unit_patient_location_id`,`pharmacist_id`,
					 `ordered_drug_id`,`ordered_drug_text`,`ordered_unit_cost`,`ordered_drug_frequency_id`,
					`ordered_doses_per_day`,`recommended_drug_id`,`recommended_drug_text`,`recommended_unit_cost`,
					 `recommended_drug_frequency_id`,`recommended_doses_per_day`,`days_of_tx`,`current_tx_day`,`facility_id`,
					 NOW() AS created_date,
					 ? AS created_by,
					 NOW() AS modified_date,
					 ? AS modified_by
					 
					 FROM clinical_intervention
					 WHERE intervention_id IN ('.$intervention_id_list.') AND 
					 		facility_id = ?';
		
		$ret_val	=	$this->db->query($sql, array($this->current_user, $this->current_user, $this->facility_id)); 
		
		return $ret_val;
	}
	/*
	 * Returns a list of current interventions
	 * @todo must filter by logged in user
	 */
	function get_intervention_list($range_date_start, $range_date_end)
	{
		
		//SELECT 
		//-- ic.risk_mitigation,
		//-- ordered_unit_cost ouc, 
		//-- odf.frequency_ratio as ofr, 
		//-- ordered_doses_per_day odpd,
		//-- ordered_unit_cost * odf.frequency_ratio * ordered_doses_per_day as Ordered_Cost,
		//-- recommended_unit_cost ouc, 
		//-- rdf.frequency_ratio as rfr, 
		//-- recommended_doses_per_day rdpd,
		//-- recommended_unit_cost * rdf.frequency_ratio * recommended_doses_per_day as recommeneded_Cost,
		//-- days_of_tx,
		//-- current_tx_day,
		//-- days_of_tx - current_tx_day as days_remaining,
		//IF (ic.risk_mitigation = 1,'RM',
		//((ordered_unit_cost * odf.frequency_ratio * ordered_doses_per_day) - (recommended_unit_cost * rdf.frequency_ratio * recommended_doses_per_day)) * (days_of_tx - current_tx_day)) AS Savings
		//
		//
		// FROM 
		//clinical_intervention ci
		//LEFT JOIN intervention_code ic on ci.intervention_code_id = ic.intervention_code_id
		//LEFT JOIN `drug_frequency` odf on ci.ordered_drug_frequency_id = odf.drug_frequency_id
		//LEFT JOIN `drug_frequency` rdf on ci.recommended_drug_frequency_id = rdf.drug_frequency_id
		//		$facility_id = $this->ion_auth->get_user()->current_facility_id;
		
		$select_fields = "intervention_id
										 ,patient_id_number
										 ,DATE_FORMAT(intervention_date, '%m/%d/%Y') AS intervention_date
										 ,code
										 ,status
										 ,physician.last_name AS physician 
										 ,unit_patient_location
										 ,pharmacist.last_name 
										 ,ordered_drug_text
										 ,CONCAT('$', FORMAT(ordered_unit_cost * odf.frequency_ratio * ordered_doses_per_day, 2)) AS OCpd
										 ,recommended_drug_text
										 ,CONCAT('$', FORMAT(recommended_unit_cost * rdf.frequency_ratio * recommended_doses_per_day, 2)) AS RCpd
										 ,IF (intervention_code.risk_mitigation = 1,'RM',((ordered_unit_cost * odf.frequency_ratio * ordered_doses_per_day) - (recommended_unit_cost * rdf.frequency_ratio * recommended_doses_per_day)) * (days_of_tx - current_tx_day)) AS savings
										 ";
		//Get the query results for the clinical interventions
		$this->db->select($select_fields, false);
		$this->db->from('clinical_intervention');
		$this->db->join('intervention_code', 'clinical_intervention.intervention_code_id = intervention_code.intervention_code_id', 'left');
		$this->db->join('physician', 'clinical_intervention.physician_id = physician.physician_id', 'left');
		$this->db->join('unit_patient_location', 'clinical_intervention.unit_patient_location_id = unit_patient_location.unit_patient_location_id', 'left');
		$this->db->join('pharmacist', 'clinical_intervention.pharmacist_id = pharmacist.pharmacist_id', 'left');
		$this->db->join('drug_frequency AS odf', 'clinical_intervention.ordered_drug_frequency_id = odf.drug_frequency_id', 'left');
		$this->db->join('drug_frequency AS rdf', 'clinical_intervention.recommended_drug_frequency_id = rdf.drug_frequency_id', 'left');
		$where = 'intervention_date BETWEEN CAST(\''.$range_date_start.'\' AS DATE) AND CAST(\''.$range_date_end.'\' AS DATE) ';
		$where .= ' AND clinical_intervention.facility_id = '.$this->facility_id;
		$this->db->where($where);
		$this->db->order_by('clinical_intervention.intervention_date', 'desc'); 
		$query = $this->db->get();
		
		
		return $query->result_array();
	}
	
	/*
	 * Takes the filter from the passed controller and determines if there should be any 
	 * additional criteria to filter for clinical interventions
	 */
	function additional_where($filter)
	{
		$ret_val	=	'';
		if (!empty($filter['duration']))
			$ret_val	.=	' AND intervention_date BETWEEN \''.str_replace('_','\' AND \'', $filter['duration']).'\'';
		if (!empty($filter['upl']))
			$ret_val	.=	' AND ci.unit_patient_location_id ='.$filter['upl'];
		if (!empty($filter['physician']))
			$ret_val	.=	' AND ci.physician_id ='.$filter['physician'];
		if (!empty($filter['pgroup']))
			$ret_val	.=	' AND pg.physician_group_id ='.$filter['pgroup'];
		if (!empty($filter['pharmacist']))
			$ret_val	.=	' AND ci.pharmacist_id ='.$filter['pharmacist'];
			
		return $ret_val;
	}
	/*
	 * Gets intervention information to be used in the display savings chart to the user
	 * 
	 * accepts filter critera to break down the report
	 */
	function get_intervention_savings($filter)
	{
		$additional_where 	=	$this->additional_where($filter);
		$query	=	'SELECT
					  ic.code,
					  Accepted,
					  Pending,
					  Rejected
					FROM 
						intervention_code ic
							LEFT JOIN 
							(
							  SELECT
							    icode.intervention_code_id,
							    sum(IFNULL((IF(ci.status=\'A\', ((ordered_unit_cost * odf.frequency_ratio * ordered_doses_per_day) - (recommended_unit_cost * rdf.frequency_ratio * recommended_doses_per_day))  * (days_of_tx - current_tx_day), NULL)), 0)) AS Accepted,
							    sum(IFNULL((IF(ci.status=\'P\', ((ordered_unit_cost * odf.frequency_ratio * ordered_doses_per_day) - (recommended_unit_cost * rdf.frequency_ratio * recommended_doses_per_day))  * (days_of_tx - current_tx_day), NULL)), 0)) AS Pending,
							    sum(IFNULL((IF(ci.status=\'R\', ((ordered_unit_cost * odf.frequency_ratio * ordered_doses_per_day) - (recommended_unit_cost * rdf.frequency_ratio * recommended_doses_per_day))  * (days_of_tx - current_tx_day), NULL)), 0)) AS Rejected
							  FROM 	
								clinical_intervention ci
									INNER JOIN intervention_code icode ON
											ci.intervention_code_id = icode.intervention_code_id
									LEFT JOIN
										physician phy ON
											ci.physician_id = phy.physician_id
											LEFT JOIN
												physician_group pg ON
													phy.physician_group_id	=	pg.physician_group_id
									LEFT JOIN
										drug_frequency AS odf on
											ci.ordered_drug_frequency_id = odf.drug_frequency_id
									LEFT JOIN
										drug_frequency AS rdf on
											ci.recommended_drug_frequency_id = rdf.drug_frequency_id
							  WHERE icode.risk_mitigation = 0 AND ci.facility_id = '.$this->facility_id.' '.$additional_where.'
							  GROUP BY icode.intervention_code_id
							) AS savings_table ON
							ic.intervention_code_id = savings_table.intervention_code_id
					ORDER BY ic.sequence';
		
		$query_result	=	$this->db->query($query);
		
		return $query_result->result_array();
		
	}
	
	function get_intervention_counts($filter)
	{
		$additional_where 	=	$this->additional_where($filter);
		$query	=	'SELECT
					  code,
					  ifnull(Accepted,0) AS Accepted,
					  ifnull(Pending,0) AS Pending,
					  ifnull(Rejected,0) AS Rejected,
					  ifnull(savings_table.Risk_Mitigation,0) as Risk_Mitigation,
					  ifnull(Accepted,0) + ifnull(Pending,0) + ifnull(Rejected,0) AS Totals
					FROM
						intervention_code ic
							LEFT JOIN 
								(
								SELECT
								    icode.intervention_code_id,
								    sum(IFNULL((IF(ci.status=\'A\',  1, NULL)), 0)) AS Accepted,
									sum(IFNULL((IF(ci.status=\'P\',  1, NULL)), 0)) AS Pending,
									sum(IFNULL((IF(ci.status=\'R\',  1, NULL)), 0)) AS Rejected,
									sum(IFNULL((IF(icode.risk_mitigation=1 and ci.status in (\'A\',\'P\',\'R\'),  1, NULL)), 0)) AS Risk_Mitigation
								FROM 	
									clinical_intervention ci
										INNER JOIN intervention_code icode ON
											ci.intervention_code_id = icode.intervention_code_id
										LEFT JOIN
											physician phy ON
												ci.physician_id = phy.physician_id
												LEFT JOIN
													physician_group pg ON
														phy.physician_group_id	=	pg.physician_group_id 
										LEFT JOIN
										drug_frequency AS odf ON
											ci.ordered_drug_frequency_id = odf.drug_frequency_id
										LEFT JOIN
											drug_frequency AS rdf ON
												ci.recommended_drug_frequency_id = rdf.drug_frequency_id
								WHERE ci.facility_id = '.$this->facility_id.' '.$additional_where.'
								GROUP BY intervention_code_id
								) AS savings_table
								ON ic.intervention_code_id = savings_table.intervention_code_id
					ORDER BY ic.sequence';
	
		$query_result	=	$this->db->query($query);
		
		return $query_result->result_array();
		
	}
	
		

	/*
	 * Returns a single intervention record based on Cliincal Intervention Id passed 
	 */
	function get_intervention_record($ci_id)
	{
		 $query = $this->db->limit(1)->get_where('clinical_intervention', array('intervention_id' => $ci_id));
		 
		 return $query->row();
	}
	
	function get_intervention_code_lookup()
	{
		$this->db->select('intervention_code_id, code');
		$this->db->order_by('sequence');
		$query = $this->db->get('intervention_code');
		
		return $query->result_array(); 
	} 

	function get_physician_lookup($current_id = false)
	{
		$this->db->select('physician_id, CONCAT_WS(\',\',`last_name`,`first_name`) AS full_name', false);
		$where 	 =	'facility_id	=	'.$this->facility_id.' AND (';
		$where	.=	'active = 1 ';
		$where	.=	$current_id	?	'OR physician_id = '.$current_id	: 	'';
		$where	.=	')';
		$this->db->where($where);
		$this->db->order_by('last_name, first_name');
		$query = $this->db->get('physician');
		
		return $query->result_array(); 
	}

	function get_unit_patient_location_lookup($current_id = false)
	{
		$this->db->select('unit_patient_location_id, unit_patient_location');
		$where 	 =	'facility_id	=	'.$this->facility_id.' AND (';
		$where	.=	'active = 1 ';
		$where	.=	$current_id	?	'OR unit_patient_location_id = '.$current_id	: 	'';
		$where	.=	')';
		$this->db->where($where);
		$this->db->order_by('unit_patient_location');
		$query = $this->db->get('unit_patient_location');
		
		return $query->result_array(); 
	}
	
	function get_pharmacist_lookup($current_id = false)
	{
		$this->db->select('pharmacist_id, CONCAT_WS(\',\',`last_name`,`first_name`) AS full_name', false);
		$where 	 =	'facility_id	=	'.$this->facility_id.' AND (';
		$where	.=	'active = 1 ';
		$where	.=	$current_id	?	'OR pharmacist_id = '.$current_id	: 	'';
		$where	.=	')';
		$this->db->where($where);
		$this->db->order_by('last_name, first_name');
		$query = $this->db->get('pharmacist');
		
		return $query->result_array(); 
	}
	
	function get_drug_frequency_lookup()
	{
		$this->db->select('drug_frequency_id, frequency, frequency_ratio');
		$query = $this->db->get('drug_frequency');
		$this->db->order_by('frequency');
		return $query->result_array(); 
	}

	
	
	function get_status_lookup()
	{
		$status_array = array(
								'-1'	=>	'', 
								'A'		=>	'Accepted',
								'P'		=>	'Pending',
								'R'		=>	'Rejected'
								);
		return $status_array; 
	}

	/*
	 * Gets intervention information to be used in the display savings chart to the user
	 * 
	 * accepts filter critera to break down the report
	 */
	function intervention_is_risk_mitigation($id)
	{
		$this->db->select('risk_mitigation');
		$query = $this->db->get_where('intervention_code', array('intervention_code_id' => $id));
		
		$row = $query->row();
		
		return $row->risk_mitigation;	
	}	
	
	/*	Return a list of durations where the user can filter the data on the CI report by date
	 *	Only returns interventions with record and returns in the following ways:
	 *		1.  Month 
	 *		2.  Quarters 1, 2, 3 or 4
	 *		3.  YTD
	 *
	 *	Does this for all years where CIs are entered
	 */ 
	function get_report_date_lookup()
	{
		$report_date_sql	=	'	
							-- By Month
							SELECT 
								CONCAT_WS(\'_\',
												CONCAT_WS(\'-\',DATE_FORMAT(intervention_date, \'%Y\'),MONTH(intervention_date),1),
												LAST_DAY(intervention_date)
										)
								AS filter_field,
								DATE_FORMAT( intervention_date, \'%Y, %M\') 	AS	date_group, 
								DATE_FORMAT( intervention_date, \'%Y\') 		AS	year_group,
								DATE_FORMAT( intervention_date, \'%m\') 		AS	mini_seq,
								1 AS mini_group
							FROM 
								clinical_intervention
							WHERE facility_id = '.$this->facility_id.'	
							GROUP BY YEAR( intervention_date ) , MONTH( intervention_date )
							
							UNION ALL
							
							-- By YEAR
							SELECT 
								CONCAT_WS(\'_\',
												CONCAT_WS(\'-\',DATE_FORMAT(intervention_date, \'%Y\'),1,1),
												CONCAT_WS(\'-\',DATE_FORMAT(intervention_date, \'%Y\'),12,31)
										)
								AS filter_field, 
								CONCAT_WS(\', \',DATE_FORMAT(intervention_date, \'%Y\'),\'YTD\') AS date_group, 
												DATE_FORMAT( intervention_date, \'%Y\' ) AS year_group,
								100 AS mini_seq,
								3 AS mini_group
							FROM 
								clinical_intervention
							WHERE facility_id = '.$this->facility_id.'
							GROUP BY YEAR( intervention_date ) 
							
							UNION ALL
							
							-- By Quarter
							SELECT 
								REPLACE(
									CASE 
										WHEN MONTH(intervention_date) BETWEEN 1 AND 3 THEN \'xxxx-01-01_xxxx-03-31\'
										WHEN MONTH(intervention_date) BETWEEN 4 AND 6 THEN \'xxxx-04-01_xxxx-06-30\'
										WHEN MONTH(intervention_date) BETWEEN 7 AND 9 THEN \'xxxx-07-01_xxxx-09-31\'
										WHEN MONTH(intervention_date) BETWEEN 10 AND 12 THEN \'xxxx-10-01_xxxx-12-31\'
										ELSE \'Else\' 
									END,
									\'xxxx\',DATE_FORMAT(intervention_date, \'%Y\')
								) 
								AS filter_field, 
								CONCAT_WS(\', \', DATE_FORMAT(intervention_date, \'%Y\'), 	
											CASE 
												WHEN MONTH(intervention_date) BETWEEN 1 AND 3 THEN \'Qtr. 1\'
												WHEN MONTH(intervention_date) BETWEEN 4 AND 6 THEN \'Qtr. 2\'
												WHEN MONTH(intervention_date) BETWEEN 7 AND 9 THEN \'Qtr. 3\'
												WHEN MONTH(intervention_date) BETWEEN 10 AND 12 THEN \'Qtr. 4\'
												ELSE \'Else\' 
											END
								) AS date_group, 
								DATE_FORMAT( intervention_date, \'%Y\' ) AS year_group,
								80 AS mini_seq,
								2 AS mini_group
							FROM 
								clinical_intervention
							WHERE facility_id = '.$this->facility_id.'
							GROUP BY DATE_FORMAT( intervention_date, \'%Y\' ),
								CASE 
									WHEN MONTH(intervention_date) BETWEEN 1 AND 3 THEN \'Qtr. 1\'
									WHEN MONTH(intervention_date) BETWEEN 4 AND 6 THEN \'Qtr. 2\'
									WHEN MONTH(intervention_date) BETWEEN 7 AND 9 THEN \'Qtr. 3\'
									WHEN MONTH(intervention_date) BETWEEN 10 AND 12 THEN \'Qtr. 4\'
									ELSE \'Else\' 
								END
							ORDER BY year_group DESC, mini_group, mini_seq, date_group
							';
		
		$query_result	=	$this->db->query($report_date_sql);
		
		return $query_result->result_array();
	}
}
/* End of file intervention_model.php */
/* Location: ./system/application/models/intervention_model.php */